USE [CallCenter]
GO

/****** Object:  StoredProcedure [dbo].[sp_add_new_Guests]    Script Date: 06/25/2013 06:56:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_add_new_Prospect]
(
/* Declare parameter to add new Prospect*/
@title varchar(10),
@Fname varchar(50),
@middle varchar(50),
@surname varchar(50),
@phone varchar(50),
@email varchar(50),
@languageID int,
@address1 varchar(50),
@address2 varchar(50),
@city varchar(50),
@province varchar(50),
@zip varchar(50),
@country varchar(50),
@communication varchar(50),
@Source varchar(50),
--
@Aereopuert int,
@Aereolinea int,
@Need_Transport int,
@Aprox_Arrival_Time varchar(50),
@Notes varchar(50),
@Pending_info varchar(50),
--
@Propiedad varchar(20),
@Internest varchar(30),
@Give_Aways varchar(30),
@Fecha varchar(30),
@Hora_Time varchar(30),
--
@Creator varchar(30),
@Product varchar(30),
@Campaign varchar(30),
@Extra_Guests varchar(20),
@Extra_Nights varchar(20),
@Kids varchar(20),
@Extra_Names varchar(20),
@Room_Details varchar(100),
@Activities varchar(100),
@arrival1 varchar(50),
@departure1 varchar(50),
@arrival2 varchar(50),
@departure2 varchar(50),
--
@NombreCard varchar(50),
@Tipo varchar(50),
@NumerodeTarjetaCard varchar(50),
@Address1_billing varchar(50),
@Address2_billing varchar(50),
@City_billing varchar(50),
@Country_billing varchar(50),
@ZipCode_billing varchar(50),
@Province_billing varchar(50),
@FechaDeExpiracion varchar(50),
@CodigodeSeguridad varchar(50),
@userId int
)
as

BEGIN

-- Insert CreaditCard if exists
DECLARE @CreditCardsID int
	IF (@NombreCard <> '' AND @Tipo <> '' AND @NumerodeTarjetaCard <> '' AND  @FechaDeExpiracion <> '' AND @CodigodeSeguridad <> '')
	BEGIN
		INSERT INTO Creditcards VALUES (@NombreCard, @Tipo, @NumerodeTarjetaCard, @Address1_billing,@Address2_billing,@City_billing,@Country_billing,@ZipCode_billing,@Province_billing, @FechaDeExpiracion, @CodigodeSeguridad)
		
		SET @CreditCardsID=(SELECT IDENT_CURRENT('Creditcards'))
	END
	ELSE 
	BEGIN
		SET @CreditCardsID = Null
	END
	

/*
*	Insert to Transports
*
*/	DECLARE @TransportID int
	IF (@Aereopuert <> 0 OR @Aereolinea <> 0 OR @Need_Transport <> -1 OR  @Aprox_Arrival_Time <> '')
	BEGIN
		INSERT INTO Transports VALUES (@Aereolinea, @Aereopuert, @Need_Transport, @Aprox_Arrival_Time, NULL,
										 NULL, NULL,NULL, @Notes, @Pending_info)
		SET @TransportID=(SELECT IDENT_CURRENT('Transports'))
	END
	ELSE 
	BEGIN
		SET @TransportID = Null
	END

-- Insert Prospect 
DECLARE @GuestID int
INSERT INTO Guests VALUES (@title,@Fname,@middle ,@surname ,@phone ,@email ,@languageID,@address1 ,
									@address2 ,@city ,@province ,@zip ,@country ,@communication,@CreditCardsID,NULL,NULL,GETDATE(),
									@TransportID,null,null,null,@Source,0)
									
-- Check insert success

SET @GuestID =(SELECT IDENT_CURRENT('Guests'))
									
									
-- Insert Visit Calendar
INSERT INTO VisitCalendar VALUES (@Propiedad,@Internest, @Give_Aways, @Fecha, @Hora_Time,@GuestID ) 

-- Insert TripDetail
INSERT INTO TripDetail VALUES (@Creator,@Product, @Campaign, @arrival1, @departure1, @arrival2, @departure2,
								@Extra_Guests, @Kids, @Extra_Nights, @Extra_Names,@Room_Details, @Activities, @GuestID ) 

	
END
GO